#!/usr/bin/python
#-*- coding: UTF-8 -*-

import os
import sys
import re
import fileinput
import MySQLdb
import urllib
import datetime
from time import strptime, strftime
import time
import string

#连接DB
db = MySQLdb.connect(host="127.0.0.1", user="root", passwd="icq$_$100", db="icq", charset="utf8")
c = db.cursor()

#获取全部频道和群组
channelSql = "SELECT cid,channel_name,owner_department_id FROM `traffic_statistics_channel`;"
groupSql = "SELECT cyid,group_name,owner_department_id FROM `traffic_statistics_group`;"
c.execute(channelSql)
channel = c.fetchall()
c.execute(groupSql)
group = c.fetchall()

#创建数据表SQL
create_table_sql = """
CREATE TABLE IF NOT EXISTS `traffic_statistics_log_%s` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `department_id_id` int(11) NOT NULL,
  `cid_id` int(11) NOT NULL,
  `cyid_id` int(11) NOT NULL,
  `aid` bigint(20) DEFAULT NULL,
  `ip` char(15) DEFAULT NULL,
  `dt` datetime DEFAULT NULL,
  `url` varchar(1000) DEFAULT NULL,
  `uv` varchar(32) DEFAULT NULL,
  `vs` varchar(32) DEFAULT NULL,
  `refer` varchar(1500) DEFAULT NULL,
  `language` varchar(50) DEFAULT NULL,
  `system` varchar(50) DEFAULT NULL,
  `browser` varchar(50) DEFAULT NULL,
  `flash_vesion` varchar(20) DEFAULT NULL,
  `resolution` varchar(10) DEFAULT NULL,
  `depath` varchar(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
"""

#根据频道ID获取部门ID
def GetDepartmentForChannel(sid):
    for cid, name, did in channel:
        if cid == sid:
            return did
        continue

#根据群组ID获取部门ID
def GetDepartmentForGroup(sid):
    for cyid, name, did in group:
        if cyid == sid:
            return did
        continue


def ImportLog():
    log_path = r"D:/tj_20110611.log"
    nowtime = datetime.datetime.today()
    logdate = nowtime - datetime.timedelta(days=1)
    year = str(logdate.year)
    month = logdate.month < 10 and '0' + str(logdate.month) or str(logdate.month)
    day = logdate.day < 10 and '0' + str(logdate.day) or str(logdate.day)
    newtablesql = create_table_sql % (year + month)

    c.execute(newtablesql)

    patternLog = re.compile(
        r'(\d+\.\d+\.\d+\.\d+)(\s-\s-\s\[)(\w+.+)(\s\+0800\])(\s\"\w+\s)(.+?)(\s.+\")(.+)(\s\".+)')
    patternCid = re.compile(r'(.+)cid=([^&]+)')
    patternCyid = re.compile(r'(.+)&cyid=([^&]+)')
    patternAid = re.compile(r'(.+)&aid=([^&]+)')
    patternUV = re.compile(r'(.+)&uv=([^&]+)')
    patternVS = re.compile(r'(.+)&vs=([^&]+)')
    patternCur = re.compile(r'(.+)&cur=([^&]+)')
    patternRefer = re.compile(r'(.+)&refer=([^&]+)')
    patternLanguage = re.compile(r'(.+)&language=([^&]+)')
    patternSystem = re.compile(r'(.+)&system=([^&]+)')
    patternBrowser = re.compile(r'(.+)&browser=([^&]+)')
    patternFlash = re.compile(r'(.+)&flashVesion=([^&]+)')
    patternResolution = re.compile(r'(.+)&resolution=([^&]+)')
    patternColorDepath = re.compile(r'(.+)&colorDepath=([^&]+)')

    error_path = "D:/err_%s.log" % (year + month + day)
    error_log = open(error_path, "a+")

    #c.execute(newtablesql)
    print "process "

    for line in fileinput.input(log_path):
        print line;
        try:
            m = patternLog.match(line)
            
            bdid = ''
            ip = m.group(1)
            olddate = m.group(3)
            dt = ConvertMySQLDate(olddate)
            mid = m.group(6)
            bcid = patternCid.match(mid)
            bcyid = patternCyid.match(mid)
            baid = patternAid.match(mid)
            if bcid:
                oldcid = bcid.group(2)
                cid = oldcid.replace('%20', '')
                sid = int(cid, 10)
                bdid = GetDepartmentForChannel(sid)
                if not bdid:
                    bdid = 0
            else:
                cid = '-9999'
            if bcyid:
                cyid = bcyid.group(2)
                sid = int(cyid, 10)
                bdid = GetDepartmentForGroup(sid)
                if not bdid:
                    bdid = 0
            else:
                cyid = '-9999'
            if baid:
                aid = baid.group(2)
            else:
                aid = 'null'
            url = m.group(8)
            url = url.replace('"', '')
            url = url.decode('utf8')

            #                url = patterncur.match(mid)
            #                if not url:
            #                    url = ''
            #                else:
            #                    url = urllib.unquote_plus(url.group(2))
            #                    url = url.decode('utf8')
            uv = patternUV.match(mid)
            if not uv:
                uv = ''
            else:
                uv = uv.group(2)

            vs = patternVS.match(mid)
            if not vs:
                vs = ''
            else:
                vs = vs.group(2)

            refer = patternRefer.match(mid)
            if not refer:
                refer = ''
            else:
                refer = urllib.unquote_plus(refer.group(2))
                refer = refer.decode('utf8')

            language = patternLanguage.match(mid)
            if not language:
                language = ''
            else:
                language = language.group(2)

            system = patternSystem.match(mid)
            if not system:
                system = ''
            else:
                system = urllib.unquote_plus(system.group(2))

            browser = patternBrowser.match(mid)
            if not browser:
                browser = ''
            else:
                browser = urllib.unquote_plus(browser.group(2))

            flash = patternFlash.match(mid)
            if not flash:
                flash = ''
            else:
                flash = flash.group(2)

            resolution = patternResolution.match(mid)
            if not resolution:
                resolution = ''
            else:
                resolution = resolution.group(2)

            depath = patternColorDepath.match(mid)
            if not depath:
                depath = ''
            else:
                depath = depath.group(2)

            if not bdid:
                bdid = 0

            #insertsql = u'insert into traffic_statistics_log_' + t1.group(
            #    2) + ' (department_id_id,cid_id,cyid_id,aid,ip,dt,url,uv,vs,refer,language,system,browser,flash_vesion,resolution,depath) values (' + str(
            #    bdid) + ',' + cid + ',' + cyid + ',' + aid + ',"' + ip + '","' + dt + '","' + url + '","' + uv + '","' + vs + '","' + refer + '","' + language + '","' + system + '","' + browser + '","' + flash + '","' + resolution + '","' + depath + '");'
            #                print insertsql
            insertsql = u'insert into traffic_statistics_log_' + year + month + ' (department_id_id,cid_id,cyid_id,aid,ip,dt,url,uv,vs,refer,language,system,browser,flash_vesion,resolution,depath) values (' + str(
                bdid) + ',' + cid + ',' + cyid + ',' + aid + ',"' + ip + '","' + dt + '","' + url + '","' + uv + '","' + vs + '","' + refer + '","' + language + '","' + system + '","' + browser + '","' + flash + '","' + resolution + '","' + depath + '");'

            c.execute(insertsql)

        except:
            #error_str+=insertsql+'\n'
            error_log.write(line)
            continue
            #error_log.write(error_str)
            #提交到数据库
    db.commit()

#转换MySQL时间格式
def ConvertMySQLDate(olddate):
    newdate = strptime(olddate, "%d/%b/%Y:%H:%M:%S")
    mysqldate = strftime("%Y-%m-%d %H:%M:%S", newdate)
    return mysqldate

ImportLog()


#关闭数据库连接
c.close()
db.close()